PostgreSQL must provide the capability for authorized users to capture, record, and log all content related to a user session.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-73021	PGS9-00-009800	SV-87673r2_rule		Medium

Description
Without the capability to capture, record, and log all content related to a user session, investigations into suspicious user activity would be hampered. Typically, this PostgreSQL capability would be used in conjunction with comparable monitoring of a user's online session, involving other software components such as operating systems, web servers and front-end user applications. The current requirement, however, deals specifically with PostgreSQL.

Description

Without the capability to capture, record, and log all content related to a user session, investigations into suspicious user activity would be hampered. Typically, this PostgreSQL capability would be used in conjunction with comparable monitoring of a user's online session, involving other software components such as operating systems, web servers and front-end user applications. The current requirement, however, deals specifically with PostgreSQL.

STIG	Date
PostgreSQL 9.x Security Technical Implementation Guide	2019-03-19

Details

Check Text ( C-73153r3_chk )
First, as the database administrator (shown here as "postgres"), verify pgaudit is installed by running the following SQL: $ sudo su - postgres $ psql -c "SHOW shared_preload_libraries" If shared_preload_libraries does not contain pgaudit, this is a finding. Next, to verify connections and disconnections are logged, run the following SQL: $ psql -c "SHOW log_connections" $ psql -c "SHOW log_disconnections" If log_connections and log_disconnections are off, this is a finding. Now, to verify that pgaudit is configured to log, run the following SQL: $ psql -c "SHOW pgaudit.log" If pgaudit.log does not contain ddl, role, read, write, function this is a finding.

Check Text ( C-73153r3_chk )

First, as the database administrator (shown here as "postgres"), verify pgaudit is installed by running the following SQL:

$ sudo su - postgres
$ psql -c "SHOW shared_preload_libraries"

If shared_preload_libraries does not contain pgaudit, this is a finding.

Next, to verify connections and disconnections are logged, run the following SQL:

$ psql -c "SHOW log_connections"
$ psql -c "SHOW log_disconnections"

If log_connections and log_disconnections are off, this is a finding.

Now, to verify that pgaudit is configured to log, run the following SQL:

$ psql -c "SHOW pgaudit.log"

If pgaudit.log does not contain ddl, role, read, write, function this is a finding.

Fix Text (F-79467r2_fix)
Note: The following instructions use the PGDATA and PGVER environment variables. See supplementary content APPENDIX-F for instructions on configuring PGDATA and APPENDIX-H for PGVER. Configure the database capture, record, and log all content related to a user session. To ensure that logging is enabled, review supplementary content APPENDIX-C for instructions on enabling logging. With logging enabled, as the database administrator (shown here as "postgres"), enable log_connections and log_disconnections: $ sudo su - postgres $ vi ${PGDATA?}/postgresql.conf log_connections = on log_disconnections = on Using pgaudit PostgreSQL can be configured to audit activity. See supplementary content APPENDIX-B for documentation on installing pgaudit. With pgaudit installed, as a database administrator (shown here as "postgres"), enable which objects required for auditing a user's session: $ sudo su - postgres $ vi ${PGDATA?}/postgresql.conf pgaudit.log = 'write, ddl, role, read, function' pgaudit.log_relation = on Now, as the system administrator, reload the server with the new configuration: # SYSTEMD SERVER ONLY $ sudo systemctl reload postgresql-${PGVER?} # INITD SERVER ONLY $ sudo service postgresql-${PGVER?} reload

Fix Text (F-79467r2_fix)

Note: The following instructions use the PGDATA and PGVER environment variables. See supplementary content APPENDIX-F for instructions on configuring PGDATA and APPENDIX-H for PGVER.

Configure the database capture, record, and log all content related to a user session.

To ensure that logging is enabled, review supplementary content APPENDIX-C for instructions on enabling logging.

With logging enabled, as the database administrator (shown here as "postgres"), enable log_connections and log_disconnections:

$ sudo su - postgres
$ vi ${PGDATA?}/postgresql.conf
log_connections = on
log_disconnections = on

Using pgaudit PostgreSQL can be configured to audit activity. See supplementary content APPENDIX-B for documentation on installing pgaudit.

With pgaudit installed, as a database administrator (shown here as "postgres"), enable which objects required for auditing a user's session:

$ sudo su - postgres
$ vi ${PGDATA?}/postgresql.conf
pgaudit.log = 'write, ddl, role, read, function'
pgaudit.log_relation = on

Now, as the system administrator, reload the server with the new configuration:

# SYSTEMD SERVER ONLY
$ sudo systemctl reload postgresql-${PGVER?}

# INITD SERVER ONLY
$ sudo service postgresql-${PGVER?} reload